﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
using System.Configuration;
using System.Data;
using StaticFunction;
using System.IO;

namespace DatabaseHelper
{
    public class DatabaseMySqlHelper:IDisposable
    {
        private const string key = "yaservnet";
        private string strConnectionString;
        private OdbcConnection objConnection;
        private OdbcCommand objCommand;
        private OdbcFactory objFactory = null;
        private OdbcParameterCollection objCollection;
        private bool boolHandleErrors;
        private string strLastError;
        private bool boolLogError;
        private string strLogFile;

        private string m_db_driver;
        private string m_db_server;
        private string m_db_port;
        private string m_db_name;
        private string m_db_id;
        private string m_db_password;

        public enum ConnectionState
        {
            KeepOpen, CloseOnExit
        }
        public DatabaseMySqlHelper(string connectionstring, Providers provider)
        {
            if (connectionstring.Substring(0, 9) == "N_ENCRYPT")
            {
                strConnectionString = connectionstring.Replace("N_ENCRYPT", "");
            }
            else
            {
                strConnectionString = Crypto.DecryptMessage(key, connectionstring);
            }
            m_db_driver = ConfigurationManager.AppSettings["Driver"];
            m_db_server = ConfigurationManager.AppSettings["ServerName"];
            m_db_name = ConfigurationManager.AppSettings["Database"];
            m_db_id = ConfigurationManager.AppSettings["UserID"];
            m_db_password = ConfigurationManager.AppSettings["Password"];

            strConnectionString = string.Format(strConnectionString, m_db_driver, m_db_server, m_db_name, m_db_id, m_db_password);


            string providername = ConfigurationManager.ConnectionStrings["connectionstring"].ProviderName;
            switch (providername)
            {
                case "System.Data.Odbc":
                    objFactory = OdbcFactory.Instance;
                    break;
            }
            objConnection = new OdbcConnection(strConnectionString);
            //objConnection = objFactory.CreateConnection();
            //objCommand = objFactory.CreateCommand();

            //objConnection.ConnectionString = strConnectionString;
            //objCommand.Connection = objConnection;

            // objCollection = new OdbcParameterCollection();
        }

        public DatabaseMySqlHelper(Providers provider)
            : this(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString, provider)
        {
        }

        public DatabaseMySqlHelper(string connectionstring)
            : this(connectionstring, Providers.SQLAnywhereAsaClient_V9)
        {
        }

        public DatabaseMySqlHelper()
            : this(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString, Providers.ConfigDefined)
        {
        }

        public bool HandleErrors
        {
            get
            {
                return boolHandleErrors;
            }
            set
            {
                boolHandleErrors = value;
            }
        }

        public string LastError
        {
            get
            {
                return strLastError;
            }
        }

        public bool LogErrors
        {
            get
            {
                return boolLogError;
            }
            set
            {
                boolLogError = value;
            }
        }

        public string LogFile
        {
            get
            {
                return strLogFile;
            }
            set
            {
                strLogFile = value;
            }
        }

        public OdbcParameter AddParameter(string name, object value)
        {
            OdbcParameter p = new OdbcParameter();
            p.ParameterName = name;
            p.Value = value;
            return objCommand.Parameters.Add(p);
        }


        public OdbcParameter AddParameterOut(string name, int size, DbType dbType, ParameterDirection direction)
        {
            OdbcParameter p = new OdbcParameter();

            p.ParameterName = name;
            p.Size = size;
            p.DbType = dbType;
            p.Direction = direction;

            return objCommand.Parameters.Add(p);
        }

        public OdbcParameter AddParameter(OdbcParameter parameter)
        {

            return objCommand.Parameters.Add(parameter);
        }

        public OdbcCommand Command
        {
            get
            {
                return objCommand;
            }
        }

        public void BeginTransaction()
        {
            if (objConnection.State == System.Data.ConnectionState.Closed)
            {
                objConnection.Open();
            }
            objCommand.Transaction = objConnection.BeginTransaction();
        }

        public void CommitTransaction()
        {
            objCommand.Transaction.Commit();
            objConnection.Close();
        }

        public void RollbackTransaction()
        {
            objCommand.Transaction.Rollback();
            objConnection.Close();
        }

        public int ExecuteNonQuery(string query)
        {
            return ExecuteNonQuery(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        public int ExecuteNonQuery(string query, CommandType commandtype)
        {
            return ExecuteNonQuery(query, commandtype, ConnectionState.CloseOnExit);
        }

        public int ExecuteNonQuery(string query, ConnectionState connectionstate)
        {
            return ExecuteNonQuery(query, CommandType.Text, connectionstate);
        }

        public int ExecuteNonQuery(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand = new OdbcCommand();
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            objCommand.Connection = objConnection;
            int i = -1;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                i = objCommand.ExecuteNonQuery();
            }
            catch (OdbcException ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    objConnection.Close();
                }
            }

            return i;
        }

        public object ExecuteScalar(string query)
        {
            return ExecuteScalar(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        public object ExecuteScalar(string query, CommandType commandtype)
        {
            return ExecuteScalar(query, commandtype, ConnectionState.CloseOnExit);
        }

        public object ExecuteScalar(string query, ConnectionState connectionstate)
        {
            return ExecuteScalar(query, CommandType.Text, connectionstate);
        }

        public object ExecuteScalar(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand = new OdbcCommand();
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            objCommand.Connection = objConnection;
            object o = null;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                o = objCommand.ExecuteScalar();
            }
            catch (OdbcException ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    objConnection.Close();
                }
            }

            return o;
        }

        public OdbcDataReader ExecuteReader(string query)
        {
            return ExecuteReader(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        public OdbcDataReader ExecuteReader(string query, CommandType commandtype)
        {
            return ExecuteReader(query, commandtype, ConnectionState.CloseOnExit);
        }

        public OdbcDataReader ExecuteReader(string query, ConnectionState connectionstate)
        {
            return ExecuteReader(query, CommandType.Text, connectionstate);
        }

        public OdbcDataReader ExecuteReader(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand = new OdbcCommand();
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            objCommand.Connection = objConnection;
            OdbcDataReader reader = null;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    reader = objCommand.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else
                {
                    reader = objCommand.ExecuteReader();
                }

            }
            catch (OdbcException ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
            }

            return reader;
        }

        public DataSet ExecuteDataSet(string query)
        {
            return ExecuteDataSet(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        public DataSet ExecuteDataSet(string query, CommandType commandtype)
        {
            return ExecuteDataSet(query, commandtype, ConnectionState.CloseOnExit);
        }

        public DataSet ExecuteDataSet(string query, ConnectionState connectionstate)
        {
            return ExecuteDataSet(query, CommandType.Text, connectionstate);
        }

        public DataSet ExecuteDataSet(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand = new OdbcCommand();
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            objCommand.Connection = objConnection;

            OdbcDataAdapter adapter = new OdbcDataAdapter();
            adapter.SelectCommand = objCommand;
            DataSet ds = new DataSet();
            try
            {
                adapter.Fill(ds);
            }
            catch (OdbcException ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    if (objConnection.State == System.Data.ConnectionState.Open)
                    {
                        objConnection.Close();
                    }
                }
            }
            return ds;
        }

        private void HandleExceptions(OdbcException ex)
        {
            if (LogErrors)
            {
                WriteToLog(ex.Message);
            }
            if (HandleErrors)
            {
                strLastError = ex.Message;
            }
            else
            {
                throw ex;
            }
        }

        private void WriteToLog(string msg)
        {
            StreamWriter writer = File.AppendText(LogFile);
            writer.WriteLine(DateTime.Now.ToString() + " - " + msg);
            writer.Close();
        }

        public void Dispose()
        {
            objConnection.Close();
            objConnection.Dispose();
            objCommand.Dispose();
        }

        public string CipherMessage(string msg)
        {
            return Crypto.DecryptMessage(key, msg);
        }
    }
}
